create database Mobile
go
use Mobile
go
create table tblStaff
(	
	id_Staff int identity(1,1) primary key,
	_userName nvarchar(30) unique,
	_password nvarchar(30),
	_permison bit
)
go
create table tblUserAccount
(
	id_UA int identity(1,1) primary key,
	_userName nvarchar(30), /*sua lai phone phai la unique*/
	_password nvarchar(30),

)
go
create table tblUserDetail
(
	id_UD int identity(1,1) primary key,
	id_UA int,
	_name nvarchar(30),
	_dateOfBirth datetime,
	_sex bit,
	_email nvarchar(100),
	_adress nvarchar(100)
	constraint FK_tblUserDetail_tblUserAccount foreign key (id_UA) references tblUserAccount(id_UA)
)
go
create table tblMobileNumber
(
	id_MN int identity(1,1) primary key,
	id_UA int,
	_phone nvarchar(20) unique, /*sua lai phone phai la unique*/
	_balance float,
	constraint FK_tblMobileNumber_tblUserAccount foreign key(id_UA) references tblUserAccount(id_UA)
)
go
create table tblServiceType
(
	id_ST int identity(1,1) primary key,
	_serviceName nvarchar(20),
	_status bit
)
go
create table tblServiceType_MobileNumber
(
	id_ST int,
	id_MN int,
	constraint FK_tblServiceType_MobileNumber_tblServerType foreign key(id_ST) references tblServiceType(id_ST),
	constraint FK_tblServiceType_MobileNumber_tblMobileNumber foreign key(id_MN) references tblMobileNumber(id_MN)
)
go
create table tblServiceDetail
(
	id_SD int identity(1,1) primary key,
	id_ST int,
	_packageName nvarchar(30),    /*cai nay phai la unique*/
	_durationDate int,
	_durationTime int,
	_description nvarchar(100),
	_discount int,
	_denomination float,
	_total float,
	_status bit
	constraint FK_tblServiceDetail_tblServiceType foreign key(id_ST) references tblServiceType(id_ST)
)
go
create table tblFeedBack
(
	id_FB int identity(1,1) primary key,
	id_UA int,
	_feedBack nvarchar(1000),
	_email nvarchar(100),
	_createdDate datetime,
	_status nvarchar(30),
	constraint FK_tblFeedBack_tblUserAccount foreign key (id_UA) references tblUserAccount(id_UA)
)

go

create table tblGuestFeedBack
(
	id_FB int identity(1,1) primary key,
	_feedBack nvarchar(1000),
	_email nvarchar(100),
	_createdDate nvarchar(30),
	_status nvarchar(30)
)
go
create table tblBill
(
	id_Bill int identity(1,1) primary key,
	id_MN int,
	id_SD int,
	id_Staff int,
	_createDate datetime,
	_paymentDate datetime,
	_billStatus bit,
	constraint FK_tblBill_tblMobileNumber foreign key(id_MN) references tblMobileNumber(id_MN),
	constraint FK_tblBill_tblStaff foreign key(id_Staff) references tblStaff(id_Staff),
	constraint FK_tblBill_tblServiceDetail foreign key(id_SD) references tblServiceDetail(id_SD)
)
go




go
insert into tblServiceType values('TopUp')
insert into tblServiceType values('SpecialRecharge')
insert into tblServiceType values('GPRS')
insert into tblServiceType values('3G')
insert into tblServiceType values('DonotDisturb')
insert into tblServiceType values('CallerTunes')
go
insert into tblServiceDetail values(1,'',0,10,'',10,500,4500);
insert into tblServiceDetail values(1,'',0,20,'',10,500,9000);
insert into tblServiceDetail values(1,'',0,30,'',10,500,13500);
insert into tblServiceDetail values(1,'',0,60,'',10,500,27000);
insert into tblServiceDetail values(1,'',0,120,'',10,500,54000);
go
